import sys
sys.path.append('/sw/systems/shared/python/')
import time
import DB
import Global
from function.count_time import count_time


class QubeDatabase:
    def __init__(self, city=Global.default['city']):
        self.cache = {}
        self.city = city
        self.DB = DB.DB()
        self.conn, self.cursor = self.DB.connect('qube', city=self.city)
        self.last_id = self.last_jobid()

    def update(self):
        if self.conn:
            self.conn.close()
        self.conn, self.cursor = self.DB.connect('qube', city=self.city)

    def last_jobid(self):
        self.cursor.execute("select * from qube.jobidmax")
        return self.cursor.fetchone()

    def view(self):
        self.cursor.execute("show tables from qube")
        return self.cursor.fetchall()

    def describe_job(self):
        self.cursor.execute("show columns from qube.job")
        return self.cursor.fetchall()

    def host(self, query_field='*', query=''):
        # local cache
        param = 'host_info_' + query_field + query
        if param in self.cache:
            return self.cache[param]

        sql = "select %s from qube.host where 1=1" % query_field
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.cache[param] = result
        return result

    @count_time
    def job(self, query_field='*', query='', refresh=False):
        # local cache
        param = 'job_info_' + query_field + query
        if not refresh and param in self.cache:
            return self.cache[param]

        if refresh: self.update()

        sql = "select %s from qube.job where 1=1" % query_field
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.cache[param] = result
        return result

    def agenda(self, jobid, query_field='*', query=''):
        try:
            jobid = int(jobid)
        except ValueError:
            raise ValueError('jobid must be integer.')

        # local cache
        param = 'job_agenda_' + str(jobid) + query_field + query
        if param in self.cache: return self.cache[param]

        sql = "select %s from %iqube.%iwork where 1=1" % (query_field, jobid >> 7, jobid)
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.cache[param] = result
        return result

    def subjob(self, jobid, query_field='*', query=''):
        try:
            jobid = int(jobid)
        except ValueError:
            raise ValueError('jobid must be integer.')

        sql = "select %s from %iqube.%isubjob where 1=1" % (query_field, jobid >> 7, jobid)
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def callback(self, jobid, query_field='*', query=''):
        try:
            jobid = int(jobid)
        except ValueError:
            raise ValueError('jobid must be integer.')

        sql = "select %s from %iqube.%icallback where 1=1" % (query_field, jobid >> 7, jobid)
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def close(self):
        self.cursor.close()
        self.conn.close()


if __name__ == '__main__':

    from function.time_wrap import time_wrap
    t = time.time()
    q = QubeDatabase()
    for i in q.view():
        print i
    for i in q.describe_job():
        print i
    print len(q.host())
    print time.time() - t
    # print 119028 >> 7
    # cursor.execute("use 929qube")
    # cursor.execute("show tables")
    # cursor.execute("select * from 929qube.119028work")
    # for i in cursor.fetchall():
    #     print i
    #
    # cursor.execute("DESC 842qube.107900callback")
    # for i, k in enumerate(cursor.fetchall()):
    #     print i, k

    # conn.close()
